Re: why doesn't an index help my simple query?

At 11:23 PM -0400 5/30/03, Tom Lane wrote:
>Peter Bierman <bierman@apple.com> writes:
>>  At 10:29 PM -0400 5/30/03, Tom Lane wrote:
>>>  Hm, why is that shown as a "filter" and not an "index condition"?  And
>>>  why is there an explicit conversion to timestamp with time zone in
>>>  there?  Better tell us about the exact data types involved here ...
>
>>  I was hoping you'd say 'hm'. :-)
>
>>  CREATE TABLE events (
>>       "time" timestamp without time zone DEFAULT
>>  ('now'::text)::timestamp(6) with time zone NOT NULL,
>
>Right.  You're getting bit by ye same olde problem of datatype mismatch:
>the planner does not realize that there is any connection between the
>types "timestamp without time zone" and "timestamp with time zone", so
>the presence of a WHERE condition expressed in terms of a timestamp-with-
>tz operator doesn't induce it to do anything that a timestamp-without-tz
>index could recognize.
>
>Short answer is you probably ought to declare events.time as timestamp
>with time zone; or if you have a *really good* reason why it should not
>be declared that way, you ought to cast what you are comparing it to
>to timestamp without tz.


Ok, two more questions then:

1) is there an easy way for me to change the type in place?

2) I created the table initially with:

CREATE TABLE events
(
  time   TIMESTAMP UNIQUE NOT NULL DEFAULT CURRENT_TIMESTAMP,

Should the plain 'timestamp' type really default to a different type
than what all the rest of the timestamp operators prefer?

Thanks!

-pmb